"""
批量生成学生信息
"""
import random
import time

import pymysql

from com.chq.common.CreateIdNumber import gen_id_card, get_names



class class_info:
    """
       班级信息
    """

    def __init__(self, school_id=None, school_name=None, district_id=None, grade_number=None, class_number=None,
                 size=None):
        self.school_id = school_id
        self.school_name = school_name
        self.district_id = district_id
        self.grade_number = grade_number
        self.class_number = class_number
        self.size = size


class student_info:
    """
       学生信息
    """

    def __init__(self, school_id=None, school_name=None, district_id=None, grade_number=None, class_number=None,
                 id_number=None,
                 student_id=None, name=None, sex=None):
        self.school_id = school_id
        self.school_name = school_name
        self.district_id = district_id
        self.grade_number = grade_number
        self.class_number = class_number
        self.id_number = id_number
        self.student_id = student_id
        self.name = name
        self.sex = sex


def get_anhui_peoples(gender, count):
    """
    随机生成安徽省身份证号码、姓名、性别 年龄12至17岁
    :param gender: 性别
    :param count: 条数
    :return: 身份证号码列表
    """
    people_list = get_names("", gender, count)
    for people in people_list:
        area_code = random.choice(
            ["340102", "340103", "340104", "340111", "340121", "340122", "340123", "340124", "340181", "340202",
             "340203",
             "340207", "340208", "340221", "340222", "340223", "340225", "340302", "340303", "340304", "340311",
             "340321",
             "340322", "340323", "340402", "340403", "340404", "340405", "340406", "340421", "340502", "340503",
             "340504",
             "340506", "340521", "340522", "340523", "340602", "340603", "340604", "340621", "340702", "340703",
             "340711",
             "340721", "340802", "340803", "340811", "340822", "340823", "340824", "340825", "340826", "340827",
             "340828",
             "340881", "341002", "341003", "341004", "341021", "341022", "341023", "341024", "341102", "341103",
             "341122",
             "341124", "341125", "341126", "341181", "341182", "341202", "341203", "341204", "341221", "341222",
             "341225",
             "341226", "341282", "341302", "341321", "341322", "341323", "341324", "341502", "341503", "341521",
             "341522",
             "341523", "341524", "341525", "341602", "341621", "341622", "341623", "341702", "341721", "341722",
             "341723",
             "341802", "341821", "341822", "341823", "341824", "341825", "341881"])
        people.id_number = gen_id_card(int(area_code), random.randint(12, 17), gender)
    return people_list


def get_peoples(district_id, gender, count):
    """
       随机生成安徽省身份证号码、姓名、性别 年龄12至17岁
       :param district_id: 区县ID
       :param gender: 性别
       :param count: 条数
       :return: 身份证号码列表
       """
    people_list = get_names("", gender, count)
    for pe in people_list:
        pe.id_number = gen_id_card(int(district_id), random.randint(12, 17), gender)
    return people_list


def insert_student(examination_id, examination_name, school_id, school_name, grade_number, class_number, student_id,
                   id_number,
                   name):
    # 获取数据连接
    connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="ems", charset="utf8mb4")
    try:
        # 获取会话指针
        with connection.cursor() as cursor:
            # 创建sql语句
            sql = "replace into `examination_student_info`(`examination_id`,`examination_name`," \
                  "`school_id`,`school_name`,`grade_number`,`class_number`,`student_id`,`id_number`,`student_name`) " \
                  "values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            # 执行sql语句
            cursor.execute(sql, (examination_id, examination_name, school_id, school_name, grade_number, class_number,
                                 student_id, id_number, name))
            # 提交
            connection.commit()
    finally:
        connection.close()


def insert_students(student_list):
    # 获取数据连接
    connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="ems", charset="utf8mb4")
    try:
        # 获取会话指针
        with connection.cursor() as cursor:
            # 创建sql语句
            sql = "replace into `examination_student_info`(`examination_id`,`examination_name`," \
                  "`school_id`,`school_name`,`grade_number`,`class_number`,`student_id`,`id_number`,`student_name`) " \
                  "values "
            i = 0
            for s in student_list:
                sql += "(%s,\"%s\",%s,\"%s\",%s,%s,%s,\"%s\",\"%s\")" % (
                    examination_id, examination_name, s.school_id, s.school_name, s.grade_number, s.class_number,
                    s.student_id, s.id_number, s.name)
                if i < len(student_list) - 1:
                    sql += ","
                i += 1
            # 执行sql语句
            cursor.execute(sql)
            # 提交
            connection.commit()
    finally:
        connection.close()


if __name__ == '__main__':
    # 获取数据连接
    connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="ems", charset="utf8mb4")
    examination_id = 247741104134144
    examination_name = "考试名称"
    try:
        # 获取会话指针
        with connection.cursor() as cursor:
            # 创建sql语句
            sql = "SELECT school_id,school_name,district_id FROM `school_info` WHERE status_flag=1 and detail_addre is null"
            # 执行sql语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
    finally:
        connection.close()
    for row in results:
        class_list = []
        school_id = row[0]
        school_name = row[1]
        district_id = row[2]
        # 年级
        grade_number = 2019
        # 班级总数 随机生成8到10个
        # 班级人数随机45至50个
        class_total = random.randint(8, 10)
        class_number = 1
        while class_number <= class_total:
            class_list.append(
                class_info(school_id, school_name, district_id, grade_number, class_number, random.randint(45, 50)))
            class_number += 1
        # 获取身份证信息
        peoples = get_peoples(district_id, random.randint(0, 1), 888)
        student_list = []
        for ci in class_list:
            print((ci.school_id, ci.district_id, ci.grade_number, ci.class_number, ci.size))
            peoples_temp = peoples[:]
            i = 0
            for p in peoples:
                if i < ci.size:
                    studentId = lambda: int(round(time.time() * 1000) * 100 + random.randint(0, 999))
                    si = student_info(ci.school_id, ci.school_name, ci.district_id, ci.grade_number, ci.class_number,
                                      p.id_number,   studentId(),  p.name)
                    student_list.append(si)
                    # insert_student(examination_id, examination_name, ci.school_id,ci.school_name,
                    #                ci.grade_number, ci.class_number,
                    #                student_id(), p.id_number, p.name)
                    peoples_temp.remove(p)
                i += 1
            peoples = peoples_temp
        # 批量更新学生信息到数据库中
        insert_students(student_list)
        # 一个学校执行完毕，更新学校状态
        connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="ems", charset="utf8mb4")
        try:
            # 获取会话指针
            with connection.cursor() as cursor:
                # 创建sql语句
                sql = "update `school_info` set detail_addre=\"\" WHERE status_flag=1 and school_id=%s"
                # 执行sql语句
                cursor.execute(sql, school_id)
                # 提交
                connection.commit()
        finally:
            connection.close()

if __name__ == '__main__1':
    for x in range(10):
        gender = random.randint(0, 1)
        peoples = get_anhui_peoples(gender, 888)
        # 获取数据连接
        connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="ems", charset="utf8mb4")
        try:
            # 获取会话指针
            with connection.cursor() as cursor:
                for people in peoples:
                    studentId = lambda: int(round(time.time() * 1000) * 100 + random.randint(0, 999))
                    # 分配到学校、班级

                    # 创建sql语句
                    sql = "replace into `examination_student_info`(`examination_id`,`examination_name`,`student_id`," \
                          "`school_id`,`school_name`,`id_number`,`student_name`,`sex`) " \
                          "values(247741104134144,\"考试名称\",%s,1,\"\",%s,%s,%s)"
                    # 执行sql语句
                    cursor.execute(sql, (studentId(), people.id_number, people.name, people.gender))
                    # 提交
                    connection.commit()
        finally:
            connection.close()
